using System;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace uk.co.tonyditchfield.tort.database.language
{
    /// <summary>
    /// SQL Server syntax engine
    /// </summary>
    internal class SQLDBSyntax : DBSyntax, IDBSyntax
    {
        /// <summary>
        /// SQL Server Database engine syntax generator
        /// </summary>
        /// <param name="obj">The object to persist</param>
        /// <param name="command">The SQLCommand object to pass parameters and statements to.</param>
        internal SQLDBSyntax(Object obj, IDbCommand command, database.language.QueryType queryType, database.providers.DbDetails details) 
            : base(obj, command, queryType, details) 
        {
            switch (queryType)
            {
                case QueryType.Insert:
                    BuildInsert();
                    break;
                case QueryType.Select:
                    BuildSelect();
                    break;
            }
        }

        #region IDBSyntax Members
        /// <summary>
        /// Constructs an SQL Server based insert statement 
        /// and binds it to the command passed in the constructor
        /// </summary>
        int BuildInsert()
        {
            int i = 1;
            beginSql.Append("INSERT INTO ");
            beginSql.Append(mapping.TableName + " (");

            foreach (string column in mapping.PersistableMapping.Keys)
            {
                ParameterName = "@" + column;
                if (i == mapping.PersistableMapping.Count)
                {
                    beginSql.Append(column + ") VALUES (");
                    endSql.Append(ParameterName + ")");
                }
                else
                {
                    beginSql.Append(column + ", ");
                    endSql.Append(ParameterName + ", ");
                }
                i++;
            }
            beginSql.Append(endSql);

            translation.PrimeKeyColumn primeKeyColumn = null;

            foreach (translation.DBColumn column in mapping.DbObject.DbColumns)
            {
                if (column.GetType().Equals(typeof(translation.PrimeKeyColumn)) && 
                    ((translation.PrimeKeyColumn)column).OutputRequired == true)
                {
                    primeKeyColumn = (translation.PrimeKeyColumn)column;
                    beginSql.Append(" SET " + primeKeyColumn.ParameterName + "= SCOPE_IDENTITY()");
                }
            }
            Utility.Logging.Log.LogDebug("SQL String: " + beginSql.ToString());
            Command.CommandText = beginSql.ToString();
            try
            {
                int rowsAffected = Command.ExecuteNonQuery();
                PrimeKey = ((SqlParameter)Command.Parameters[primeKeyColumn.ParameterName]).Value;
                
                return rowsAffected;
            }
            catch (InvalidOperationException ie)
            {
                throw new exception.PersistenceException(ie.Message, ie);
            }
            catch (SqlException se)
            {
                throw new exception.PersistenceException(se.Message, se);
            }
            catch (Exception e)
            {
                throw new exception.PersistenceException(e.Message, e);
            }
        }

        int IDBSyntax.BuildDelete()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        object BuildSelect()
        {
            beginSql.Append("SELECT ");

            int i = 1;
            foreach (translation.DBColumn column in mapping.DbObject.DbColumns)
            {
                if (i == mapping.DbObject.DbColumns.Count)
                {
                    beginSql.Append(column.ColumnName);
                    beginSql.Append(" FROM ");
                    beginSql.Append(mapping.TableName);
                    beginSql.Append(" WHERE ");
                }
                else
                {
                    beginSql.Append(column.ColumnName + ", ");
                }
                if (column.GetType().Equals(typeof(translation.PrimeKeyColumn)))
                {
                    endSql.Append(((translation.PrimeKeyColumn)column).ColumnName);
                    endSql.Append(" = ");
                    endSql.Append(((translation.PrimeKeyColumn)column).ParameterName);
                }
                i++;
            }
            beginSql.Append(endSql);

            Utility.Logging.Log.LogDebug("SQL String: " + beginSql.ToString());
            Command.CommandText = beginSql.ToString();
            try
            {
                IDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    int j = 0;
                    foreach (PropertyInfo propertyInfo in Obj.GetType().GetProperties())
                    {
                        if (propertyInfo.GetCustomAttributes(typeof(attributes.DBFieldAttribute), false).Length > 0)
                        {
                            if (reader.IsDBNull(j))
                            {
                                propertyInfo.SetValue(Obj, null, null);
                            }
                            else
                            {
                                // Its a simple object.....
                                if (propertyInfo.GetCustomAttributes(typeof(attributes.DBFieldAttribute), false)[0].GetType()
                                    != typeof(attributes.LoadRequirementAttribute))
                                {
                                    propertyInfo.SetValue(Obj, reader.GetValue(j), null);
                                }
                            }
                            j++;
                        }
                    }
                }
                reader.Close();
                return Obj;
            }
            catch (SqlException se)
            {
                throw new exception.PersistenceException(se.Message, se);
            }
            catch (System.Data.SqlTypes.SqlTypeException ste)
            {
                throw new exception.PersistenceException(ste.Message, ste);
            }
        }

        int IDBSyntax.BuildUpdate()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
